﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_MailInClassGetBriefInfo]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_MailInClassGetBriefInfo];
GO
-------------------------------------------------------------------------------
-- sproc_MailInClassGetBriefInfo
-------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[sproc_MailInClassGetBriefInfo]
(
    @ClassID              int,
    @Username             char(20),
    @MailFolderType     int     
)
AS
--得到某用户某收件夹中的邮件简要信息
--1    收件
--2    我的发件
--3    我的回收
if @MailFolderType=1
    SELECT MailID,(SELECT RealName FROM UDS_Staff a where a.Staff_Name=MailSender) as MailSender,(SELECT RealName  FROM UDS_Staff a  where a.Staff_Name=MailReceiver)  as MailReceiver,MailSendDate,MailSubject,MailReadFlag,MailReceiverStr,(select count(*) from TabMailAttachFiles where MailID=TabMailList.MailID) AS attnumber
    FROM TabMailList
    WHERE    MailReceiver = @Username AND MailFolderType = @MailFolderType  AND ClassID = @ClassID ORDER BY MailID DESC
if @MailFolderType=2
    SELECT MailID,(SELECT RealName FROM UDS_Staff a where a.Staff_Name=MailSender) as MailSender,(SELECT RealName FROM UDS_Staff a where a.Staff_Name=MailReceiver)  as MailReceiver,MailSendDate,MailSubject,MailReadFlag,MailReceiverStr,(select count(*) from TabMailAttachFiles where MailID=TabMailList.MailID) AS attnumber 
    FROM TabMailList
    WHERE    MailSender = @Username AND MailFolderType = @MailFolderType   AND ClassID = @ClassID ORDER BY MailID DESC
if @MailFolderType=3
     SELECT MailID,(SELECT RealName FROM UDS_Staff a where a.Staff_Name=MailSender) as MailSender,(SELECT RealName FROM UDS_Staff a where a.Staff_Name=MailReceiver)  as MailReceiver,MailSendDate,MailSubject,MailReadFlag,MailReceiverStr,(select count(*) from TabMailAttachFiles where MailID=TabMailList.MailID) AS attnumber 
    FROM TabMailList
    WHERE    MailSender = @Username AND MailFolderType = @MailFolderType   AND ClassID = @ClassID ORDER BY MailID DESC